package com.ndbg.utils;

import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.poi.ss.usermodel.*;

import java.io.IOException;
import java.io.InputStream;

/**
 * @author chenXB
 * @createTime 2024年12月06日 16:20:00
 */
public class FTPUtil {
    private static String server = "uisftp.chinaums.com";
    private static int port = 21;
    private static String username = "101900-898530786510003";
    private static String password = "LJgcglj20241206.";



    public static InputStream getFileStream() {
        FTPClient ftpClient = new FTPClient();
        try {
            ftpClient.connect(server, port);
            ftpClient.login(username, password);
            ftpClient.enterLocalPassiveMode(); // 被动模式
            ftpClient.setFileType(FTP.BINARY_FILE_TYPE);
            // 获取文件输入流
            return ftpClient.retrieveFileStream("build/898530786510003_20241205_9.xls");
        } catch (IOException ex) {
            ex.printStackTrace();
            try {
                if (ftpClient.isConnected()) {
                    ftpClient.logout();
                    ftpClient.disconnect();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static void main(String[] args) throws IOException {
        InputStream inputStream = getFileStream();
        parseExcel(inputStream);
    }

    public static void parseExcel(InputStream inputStream) throws IOException {
        // 解析Excel文件
        try (Workbook workbook = WorkbookFactory.create(inputStream)) {



            // 从第4行开始读取数据（索引从0开始，第4行为索引3）
            int startRow = 3;
            Sheet sheet = workbook.getSheetAt(0);
            int rowIndex = startRow;
            for (rowIndex = startRow; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;
                // 获取当前行第一个单元格内容
                Cell firstCell = row.getCell(0);
                if (firstCell == null) {
                    continue;
                }
                String cellValue = firstCell.getStringCellValue().trim();
                if ("汇总信息".equals(cellValue) || cellValue.contains("汇总信息")) {
                    System.out.println("发现'汇总'关键字，停止读取。");
                    break;
                }
//                Cell cell = row.getCell(2);
//                for (Cell cell : row) {
//                    System.out.print(cell.getStringCellValue());
//                }
            }

            for (int i = rowIndex + 3; i<= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                System.out.print(cell.getDateCellValue() + "\t");
                            } else {
                                System.out.print(cell.getNumericCellValue() + "\t");
                            }
                            break;
                        case BOOLEAN:
                            System.out.print(cell.getBooleanCellValue() + "\t");
                            break;
                        default:
                            System.out.print(" \t");
                            break;
                    }
                }
            }
        }
    }

    public static void closeConnection(FTPClient ftpClient) {
        try {
            if (ftpClient != null && ftpClient.isConnected()) {
                ftpClient.logout();
                ftpClient.disconnect();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
